package com.aixin.csopsupplier.repository;

import com.aixin.csopsupplier.domain.ServiceDef;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface ServiceDefRepository extends JpaRepository<ServiceDef,Long> {

    @Query(value = "SELECT * from servicedef  where serviceStatus < 2 " +
            "and (:supplierName is  null or supplierName = :supplierName) " +
            "and (:serviceName is  null or serviceName = :serviceName) ",nativeQuery = true)
    Page<ServiceDef> getServiceDef(@Param("supplierName") String supplierName, @Param("serviceName") String serviceName,Pageable pageable);


    @Query(value = "SELECT * from servicedef  where serviceStatus < 2 " +
            "and (:serviceName is  null or serviceName like CONCAT ('%', :serviceName ,'%') ) ",nativeQuery = true)
    List<ServiceDef> getServiceByName(@Param("serviceName") String serviceName);

    @Query(value = "select sd.supplierID from ServiceDef sd join ServiceLevelDef sld on sd.serviceID=sld.serviceID " +
            "where sld.equityLevelID in :levelID")
    List<ServiceDef> getServiceDefByEquityLevelIDList(@Param("levelID") List<String> levelID);

    @Query(value = "select sd from ServiceDef sd " +
            "join ServiceLevelDef sld on sld.serviceID=sd.serviceID " +
            "join CustomerEquity ce on ce.equityID=sld.equityID and ce.custLevelID=sld.equityLevelID " +
            "where ce.customerID=:customerID and sd.serviceStatus<2")
    List<ServiceDef> getCustomerServices(@Param("customerID") String customerID);

    List<ServiceDef> findAllBySupplierIDAndServiceStatusLessThan(String supplierID, Integer status);

    @Query(value = "select sd from ServiceDef sd " +
            "join ServiceLevelDef sld on sld.serviceID=sd.serviceID " +
            "where sd.serviceStatus<2 " +
            "and ((:supplierID is null) or (sd.supplierID=:supplierID)) " +
            "and ((:equityID is null) or (sld.equityID=:equityID))" +
            "and ((:equityLevelID is null) or (sld.equityLevelID=:equityLevelID))")
    List<ServiceDef> findSpecService(@Param("supplierID") String supplierID, @Param("equityID") String equityID, @Param("equityLevelID") String equityLevelID);
}
